package cp3120.dao.mysql;

import cp3120.dao.DAOFactory;
import cp3120.dao.PurchaseDAO;
import cp3120.model.Purchase;
import cp3120.model.Ticket;
import cp3120.model.pricing.PricingStrategy;
import cp3120.model.pricing.PricingStrategyFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.LinkedList;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * User: Rowan Spence
 * Date: 26/05/2009
 * Time: 9:18:58 PM
 */
public class MySQLPurchaseDAO implements PurchaseDAO {
    private Connection db;
    private PreparedStatement getPurchase;
    private PreparedStatement getAllTickets;
    private PreparedStatement getUserPurchases;
    private PreparedStatement addPurchase;
    private PreparedStatement addTicket;
    private PreparedStatement getPricingStrategys;
    private PreparedStatement getPricingVars;
    private PreparedStatement addPricingStrategy;
    private PreparedStatement deletePricingStrategy;
    private PreparedStatement updatePricingStrategy;
    private PreparedStatement addPricingVar;
    private PreparedStatement deletePricingVar;
    private PreparedStatement deleteAllPricingVars;
    private MySQLUserDAO userDAO;

    /**
     * constructor, sets up database to querry, prepares My SQL statements and creates a userDAO object
     * @param db the database connection that will be querried
     */
    public MySQLPurchaseDAO(Connection db) {
        this.db = db;
        userDAO = new  MySQLUserDAO(db);
        prepareStatements();
    }

    private void prepareStatements() {
        try {
            getPurchase = db.prepareStatement("SELECT * FROM Purchase WHERE purchaseID = ?");
            getAllTickets = db.prepareStatement("SELECT flightID, userID, custFName, custLName, price FROM tickets WHERE userID = ?");
            getUserPurchases = db.prepareStatement("SELECT * FROM Purchase WHERE userID = ?");
            addPurchase = db.prepareStatement("INSERT INTO purchase (purchaseID, userID, purchaseDate) VALUES (null, ?, null)", Statement.RETURN_GENERATED_KEYS);
            addTicket = db.prepareStatement("INSERT INTO tickets (flightID, purchaseID, custFName, custLName, price) VALUES (?, ?, ?, ?, ?)");
            getPricingStrategys = db.prepareStatement("SELECT id, name, type FROM pricing_strategy");
            getPricingVars = db.prepareStatement("SELECT name, value FROM pricing_strategy_variable WHERE strategy_id = ?");
            addPricingStrategy = db.prepareStatement("INSERT INTO pricing_strategy (name, type) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
            deletePricingStrategy = db.prepareStatement("DELETE FROM pricing_strategy WHERE id = ?");
            updatePricingStrategy = db.prepareStatement("UPDATE pricing_strategy SET name = ?, type = ? WHERE id = ?");
            addPricingVar = db.prepareStatement("INSERT INTO pricing_strategy_variable (strategy_id, name, value) VALUES (?, ?, ?)");
            deletePricingVar = db.prepareStatement("DELETE FROM pricing_strategy_variable WHERE strategy_id = ? AND name = ?");
            deleteAllPricingVars = db.prepareStatement("DELTE FROM pricing_strategy_variable WHERE strategy_id = ?");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Purchase buildPurchase(ResultSet result) throws SQLException {
        Purchase p = new Purchase(result.getInt("purchaseID"),
                                  userDAO.getUserByID(result.getInt("userID")),
                                  result.getString("purchaseDate"));

        getAllTickets.setInt(1, p.getPurchaseID());
        ResultSet resultset = getAllTickets.executeQuery();

        while (resultset.next()) {
            Ticket t = new Ticket();
            t.setFlight(DAOFactory.getFlightDAO().getFlight(resultset.getInt(1)));
            t.setFirstName(resultset.getString(3));
            t.setLastName(resultset.getString(4));
            t.setPrice(resultset.getFloat(5));

            p.add(t);
        }

        return p;
    }

    /**
     * gets a purchase fromt he database
     * @param purchaseID the Id of the purchase to be retrieved
     * @return a purchase object, else null if it could not be retrieved
     */
    public Purchase getPurchase(int purchaseID) {
        try {
            getPurchase.setInt(1, purchaseID);
            ResultSet result = getPurchase.executeQuery();
            if (result.next()) {
                return buildPurchase(result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * gets all purcahses made by a specified user
     * @param userID the ID of the user who's purchases will be retrieved
     * @return a list of purchases, else null if none are found
     */
    public List<Purchase> getUserPurchases(int userID) {
        LinkedList<Purchase> purchases = new LinkedList<Purchase>();
        try {
            getUserPurchases.setInt(1, userID);
            ResultSet result = getUserPurchases.executeQuery();
            while (result.next()) {
                purchases.add(buildPurchase(result));
            }

            if (!purchases.isEmpty()) {
                return purchases;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * adds a new purchase to the database
     * @param purchase the purcahse to be added to the database
     * @return true if the purcahse was added, else false
     */
    public boolean addPurchase(Purchase purchase) {

        try {
            addPurchase.setInt(1, purchase.getUser().getUserID());
            boolean status = addPurchase.execute();

            ResultSet rs = addPurchase.getGeneratedKeys();
            int id = 0;
            if (rs != null && rs.next()) {
                id = rs.getInt(1);
            }

            for (Ticket t: purchase.getTickets()) {
                //INSERT INTO tickets (flightID, userID, custFName, custLName) VALUES (?, ?, ?, ?)");
                addTicket.setInt(1, t.getFlight().getFlightID());
                addTicket.setInt(2, id);
                addTicket.setString(3, t.getFirstName());
                addTicket.setString(4, t.getLastName());
                addTicket.setFloat(5, t.getPrice());

                boolean ticketStatus = addTicket.execute();
                status = status && ticketStatus;
            }

            return status;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    private Map<String, String> getVars(ResultSet PSRes) {
        Map<String, String> vars = new HashMap<String, String>();
        try {
            getPricingVars.setInt(1, PSRes.getInt(1));

            ResultSet res = getPricingVars.executeQuery();

            while (res.next()) {
                vars.put(res.getString(1), res.getString(2));
            }

        } catch (SQLException ex) {
            Logger.getLogger(MySQLPurchaseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return vars;
    }

    public List<PricingStrategy> getPricingStrategys() {
        try {
            ResultSet result = getPricingStrategys.executeQuery();
            List<PricingStrategy> comp = new ArrayList<PricingStrategy>();

            while (result.next()) {
                PricingStrategy ps = PricingStrategyFactory.createPricingStrategy(result.getString(3), getVars(result));
                ps.setId(result.getInt(1));
                ps.setName(result.getString(2));
                comp.add(ps);
            }

            return comp;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean addPricingStrategy(PricingStrategy strategy) {
        try {
            // addPricingStrategy = db.prepareStatement("INSERT INTO pricing_strategy (name, type) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
            // addPricingVar = db.prepareStatement("INSERT INTO pricing_strategy_variable (strategy_id, name, value) VALUES (?, ?, ?)");
            addPricingStrategy.setString(1, strategy.getName());
            addPricingStrategy.setString(2, strategy.getClass().getCanonicalName());

            addPricingStrategy.execute();

            ResultSet rs = addPricingStrategy.getGeneratedKeys();
            int id = 0;
            if (rs != null && rs.next()) {
                id = rs.getInt(1);
            }

            for (String key: strategy.getVars().keySet()) {
                String value = strategy.getVars().get(key);

                addPricingVar.setInt(1, id);
                addPricingVar.setString(2, key);
                addPricingVar.setString(3, value);

                addPricingVar.execute();
            }

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLPurchaseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean removePricingStrategy(PricingStrategy strategy) {
        try {
            //deletePricingStrategy = db.prepareStatement("DELETE FROM pricing_strategy WHERE id = ?");
            //deletePricingVar = db.prepareStatement("DELETE FROM pricing_strategy_variable WHERE strategy_id = ? AND name = ?");
            deletePricingStrategy.setInt(1, strategy.getId());
            deletePricingStrategy.execute();
            for (String key: strategy.getVars().keySet()) {
                deletePricingVar.setInt(1, strategy.getId());
                deletePricingVar.setString(2, key);

                deletePricingVar.execute();
            }

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLPurchaseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean updatePricingStrategy(PricingStrategy strategy) {
        try {
            //updatePricingStrategy = db.prepareStatement("UPDATE pricing_strategy SET name = ?, type = ? WHERE id = ?");
            updatePricingStrategy.setString(1, strategy.getName());
            updatePricingStrategy.setString(2, strategy.getClass().getCanonicalName());
            updatePricingStrategy.setInt(3, strategy.getId());
            updatePricingStrategy.execute();

            deleteAllPricingVars.setInt(1, strategy.getId());
            deleteAllPricingVars.execute();

            for (String key: strategy.getVars().keySet()) {
                String value = strategy.getVars().get(key);

                addPricingVar.setInt(1, strategy.getId());
                addPricingVar.setString(2, key);
                addPricingVar.setString(3, value);

                addPricingVar.execute();
            }

        } catch (SQLException ex) {
            Logger.getLogger(MySQLPurchaseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

}
